package com.un.ebs.invoice.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.un.ebs.invoice.domain.InvoiceNote;
import com.un.ebs.invoice.domain.InvoiceNoteByPurchase;
import com.un.ebs.invoice.dto.AccountAgeDto;
import com.un.ebs.invoice.dto.InvoiceBalanceDto;
import com.un.ebs.invoice.dto.InvoiceNoteByServiceResult;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * (InvoiceNote)表数据库访问层
 *
 * @author liubo
 * @since 2020-09-27 11:06:42
 */
public interface InvoiceNoteMapper extends BaseMapper<InvoiceNote> {

    @Update("update tb_invoice_note set finance_id=#{financeId},finance_code=#{financeCode} where id = #{id}")
    void updateFinanceStatus(String id, String financeId, String financeCode);

    @Select("select ISNULL(MAX(code),0)+1 from tb_invoice_note")
    int maxCode();

    @Select("SELECT ent_id,ent_name,SUM(invoice_not_qty) can_qty,SUM(invoice_not_money) can_money,tenant_id FROM [v_inventory_note_item_by_sale] WHERE invoice_not_money > 0 GROUP BY ent_id,ent_name,tenant_id")
    List<InvoiceBalanceDto> queryInvoiceBalanceBySale();

    @Select("select * from v_invoice_note_by_purchase where invoice_note_id = #{invoiceNoteId}")
    InvoiceNoteByPurchase queryPurchaseById(String invoiceNoteId);

    @Select("SELECT t.ent_id,t.ent_name,t.balance_money,t.cash_30 AS cash30,t.cash_60 AS cash60,t.cash_90 AS cash90,t.cash_120 AS cash120,t.cash_150 AS cash150,t.cash_180 AS cash180,t.cash_270 AS cash270,t.cash_360 AS cash360,t.cash_720 AS cash720,t.cash_1080 AS cash1080 FROM dbo.v_account_age_ext t")
    List<AccountAgeDto> queryAccountAge();

    @Select("with vOrder as (select a.id as order_id,b.sku_id,sum(b.qty*b.price) as order_money ,a.tenant_id from tb_sale_order a inner join tb_sale_order_item b on a.id=b.order_id and a.submit_status=1 and a.is_delete=0 group by a.id,b.sku_id,a.tenant_id ) ,vInvoice as(select a.sale_order_id as order_id,a.sku_id,sum(a.invoice_money) as invoice_money,b.tenant_id from  tb_invoice_note_by_sale a inner join  tb_invoice_note b on a.note_id=b.id where  \t\tb.is_delete=0 \t\tand b.submit_status=1 \tgroup by a.sale_order_id,a.sku_id,b.tenant_id ) select top 1 a.order_id,a.sku_id,a.order_money,isnull(b.invoice_money,0) as invoice_money,a.order_money-isnull(b.invoice_money,0) as can_invoice_money,a.tenant_id from vOrder a left join vInvoice b on a.order_id=b.order_id and a.sku_id=b.sku_id and a.tenant_id=b.tenant_id where a.order_id=#{saleOrderId} and a.sku_id=#{skuId}")
    InvoiceNoteByServiceResult queryCanServiceInvoice(@Param("saleOrderId") String saleOrderId, @Param("skuId") String skuId);

    @Select("select * from v_sale_order_invoice_by_service where tenant_id=#{tenantId}")
    List<InvoiceNoteByServiceResult> queryCanServiceInvoiceList(@Param("tenantId") String tenantId);

}